/**
 * Copyright (c) 2011-2012, James Zhan 詹波 (jfinal@126.com).
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

package com.wholesale.plugin.activerecord.dialect;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import com.wholesale.plugin.activerecord.ActiveRecordException;
import com.wholesale.plugin.activerecord.CPI;
import com.wholesale.plugin.activerecord.DbKit;
import com.wholesale.plugin.activerecord.Model;
import com.wholesale.plugin.activerecord.ModelBuilder;
import com.wholesale.plugin.activerecord.Page;
import com.wholesale.plugin.activerecord.Record;
import com.wholesale.plugin.activerecord.TableInfo;

/**
 * AnsiSqlDialect. Try to use ANSI SQL dialect with ActiveRecordPlugin.
 * <p>
 * A clever person solves a problem. A wise person avoids it.
 */
public class AnsiSqlDialect extends Dialect {

    public String forTableInfoBuilderDoBuildTableInfo(String tableName) {
        return "select * from " + tableName + " where 1 = 2";
    }

    public void forModelSave(TableInfo tableInfo,
                             Map<String, Object> attrs,
                             StringBuilder sql,
                             List<Object> paras) {
        sql.append("insert into ").append(tableInfo.getTableName()).append("(");
        StringBuilder temp = new StringBuilder(") values(");
        for (Entry<String, Object> e : attrs.entrySet()) {
            String colName = e.getKey();
            if (tableInfo.hasColumnLabel(colName)) {
                if (paras.size() > 0) {
                    sql.append(", ");
                    temp.append(", ");
                }
                sql.append(colName);
                temp.append("?");
                paras.add(e.getValue());
            }
        }
        sql.append(temp.toString()).append(")");
    }

    public String forModelDeleteById(TableInfo tInfo) {
        String pKey = tInfo.getPrimaryKey();
        StringBuilder sql = new StringBuilder(45);
        sql.append("delete from ");
        sql.append(tInfo.getTableName());
        sql.append(" where ").append(pKey).append(" = ?");
        return sql.toString();
    }

    public void forModelUpdate(TableInfo tableInfo,
                               Map<String, Object> attrs,
                               Set<String> modifyFlag,
                               String pKey,
                               Object id,
                               StringBuilder sql,
                               List<Object> paras) {
        sql.append("update ").append(tableInfo.getTableName()).append(" set ");
        for (Entry<String, Object> e : attrs.entrySet()) {
            String colName = e.getKey();
            if (!pKey.equalsIgnoreCase(colName)
                && modifyFlag.contains(colName)
                && tableInfo.hasColumnLabel(colName)) {
                if (paras.size() > 0)
                    sql.append(", ");
                sql.append(colName).append(" = ? ");
                paras.add(e.getValue());
            }
        }
        sql.append(" where ").append(pKey).append(" = ?");
        paras.add(id);
    }

    public String forModelFindById(TableInfo tInfo, String columns) {
        StringBuilder sql = new StringBuilder("select ");
        if (columns.trim().equals("*")) {
            sql.append(columns);
        } else {
            String[] columnsArray = columns.split(",");
            for (int i = 0; i < columnsArray.length; i++) {
                if (i > 0)
                    sql.append(", ");
                sql.append(columnsArray[i].trim());
            }
        }
        sql.append(" from ");
        sql.append(tInfo.getTableName());
        sql.append(" where ").append(tInfo.getPrimaryKey()).append(" = ?");
        return sql.toString();
    }

    public String forDbFindById(String tableName, String primaryKey, String columns) {
        StringBuilder sql = new StringBuilder("select ");
        if (columns.trim().equals("*")) {
            sql.append(columns);
        } else {
            String[] columnsArray = columns.split(",");
            for (int i = 0; i < columnsArray.length; i++) {
                if (i > 0)
                    sql.append(", ");
                sql.append(columnsArray[i].trim());
            }
        }
        sql.append(" from ");
        sql.append(tableName.trim());
        sql.append(" where ").append(primaryKey).append(" = ?");
        return sql.toString();
    }

    public String forDbDeleteById(String tableName, String primaryKey) {
        StringBuilder sql = new StringBuilder("delete from ");
        sql.append(tableName.trim());
        sql.append(" where ").append(primaryKey).append(" = ?");
        return sql.toString();
    }

    public void forDbSave(StringBuilder sql, List<Object> paras, String tableName, Record record) {
        sql.append("insert into ");
        sql.append(tableName.trim()).append("(");
        StringBuilder temp = new StringBuilder();
        temp.append(") values(");

        for (Entry<String, Object> e : record.getColumns().entrySet()) {
            if (paras.size() > 0) {
                sql.append(", ");
                temp.append(", ");
            }
            sql.append(e.getKey());
            temp.append("?");
            paras.add(e.getValue());
        }
        sql.append(temp.toString()).append(")");
    }

    public void forDbUpdate(String tableName,
                            String primaryKey,
                            Object id,
                            Record record,
                            StringBuilder sql,
                            List<Object> paras) {
        sql.append("update ").append(tableName.trim()).append(" set ");
        for (Entry<String, Object> e : record.getColumns().entrySet()) {
            String colName = e.getKey();
            if (!primaryKey.equalsIgnoreCase(colName)) {
                if (paras.size() > 0) {
                    sql.append(", ");
                }
                sql.append(colName).append(" = ? ");
                paras.add(e.getValue());
            }
        }
        sql.append(" where ").append(primaryKey).append(" = ?");
        paras.add(id);
    }

    /**
     * SELECT * FROM subject t1 WHERE (SELECT count(*) FROM subject t2 WHERE
     * t2.id < t1.id AND t2.key = '123') > = 10 AND (SELECT count(*) FROM
     * subject t2 WHERE t2.id < t1.id AND t2.key = '123') < 20 AND t1.key =
     * '123'
     */
    public void forPaginate(StringBuilder sql,
                            int pageNumber,
                            int pageSize,
                            String select,
                            String sqlExceptSelect) {
        throw new ActiveRecordException("Your should not invoke this method because takeOverDbPaginate(...) will take over it.");
    }

    public boolean isTakeOverDbPaginate() {
        return true;
    }

    @SuppressWarnings("rawtypes")
    public Page<Record> takeOverDbPaginate(Connection conn,
                                           int pageNumber,
                                           int pageSize,
                                           String select,
                                           String sqlExceptSelect,
                                           Object... paras) throws SQLException {
        long totalRow = 0;
        int totalPage = 0;
        List result = CPI.query(conn,
                                "select count(*) " + DbKit.replaceFormatSqlOrderBy(sqlExceptSelect),
                                paras);
        int size = result.size();
        if (size == 1)
            totalRow = ((Number) result.get(0)).longValue();
        else if (size > 1)
            totalRow = result.size();
        else
            return new Page<Record>(new ArrayList<Record>(0), pageNumber, pageSize, 0, 0);

        totalPage = (int) (totalRow / pageSize);
        if (totalRow % pageSize != 0) {
            totalPage++;
        }

        StringBuilder sql = new StringBuilder();
        sql.append(select).append(" ").append(sqlExceptSelect);
        PreparedStatement pst = conn.prepareStatement(sql.toString(),
                                                      ResultSet.TYPE_FORWARD_ONLY,
                                                      ResultSet.CONCUR_READ_ONLY);
        for (int i = 0; i < paras.length; i++) {
            pst.setObject(i + 1, paras[i]);
        }
        ResultSet rs = pst.executeQuery();

        // move the cursor to the start
        int offset = pageSize * (pageNumber - 1);
        for (int i = 0; i < offset; i++)
            if (!rs.next())
                break;

        List<Record> list = buildRecord(rs, pageSize);
        if (rs != null)
            rs.close();
        if (pst != null)
            pst.close();
        return new Page<Record>(list, pageNumber, pageSize, totalPage, (int) totalRow);
    }

    private List<Record> buildRecord(ResultSet rs, int pageSize) throws SQLException {
        List<Record> result = new ArrayList<Record>();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        String[] labelNames = new String[columnCount + 1];
        int[] types = new int[columnCount + 1];
        buildLabelNamesAndTypes(rsmd, labelNames, types);
        for (int k = 0; k < pageSize && rs.next(); k++) {
            Record record = new Record();
            Map<String, Object> columns = record.getColumns();
            for (int i = 1; i <= columnCount; i++) {
                Object value;
                if (types[i] < Types.BLOB)
                    value = rs.getObject(i);
                else if (types[i] == Types.CLOB)
                    value = ModelBuilder.handleClob(rs.getClob(i));
                else if (types[i] == Types.NCLOB)
                    value = ModelBuilder.handleClob(rs.getNClob(i));
                else if (types[i] == Types.BLOB)
                    value = ModelBuilder.handleBlob(rs.getBlob(i));
                else
                    value = rs.getObject(i);

                columns.put(labelNames[i], value);
            }
            result.add(record);
        }
        return result;
    }

    private void buildLabelNamesAndTypes(ResultSetMetaData rsmd, String[] labelNames, int[] types)
            throws SQLException {
        for (int i = 1; i < labelNames.length; i++) {
            labelNames[i] = rsmd.getColumnLabel(i);
            types[i] = rsmd.getColumnType(i);
        }
    }

    public boolean isTakeOverModelPaginate() {
        return true;
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    public Page<? extends Model> takeOverModelPaginate(Class<? extends Model> modelClass,
                                                       int pageNumber,
                                                       int pageSize,
                                                       String select,
                                                       String sqlExceptSelect,
                                                       Object... paras) {
        Connection conn = null;
        try {
            conn = DbKit.getConnection();
            long totalRow = 0;
            int totalPage = 0;
            List result = CPI.query(conn,
                                    "select count(*) "
                                            + DbKit.replaceFormatSqlOrderBy(sqlExceptSelect),
                                    paras);
            int size = result.size();
            if (size == 1)
                totalRow = ((Number) result.get(0)).longValue(); // totalRow =
                                                                 // (Long)result.get(0);
            else if (size > 1)
                totalRow = result.size();
            else
                return new Page(new ArrayList(0), pageNumber, pageSize, 0, 0); // totalRow
                                                                               // =
                                                                               // 0;

            totalPage = (int) (totalRow / pageSize);
            if (totalRow % pageSize != 0) {
                totalPage++;
            }

            // --------
            StringBuilder sql = new StringBuilder();
            sql.append(select).append(" ").append(sqlExceptSelect);
            PreparedStatement pst = conn.prepareStatement(sql.toString(),
                                                          ResultSet.TYPE_FORWARD_ONLY,
                                                          ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < paras.length; i++) {
                pst.setObject(i + 1, paras[i]);
            }
            ResultSet rs = pst.executeQuery();

            // move the cursor to the start
            int offset = pageSize * (pageNumber - 1);
            for (int i = 0; i < offset; i++)
                if (!rs.next())
                    break;

            List list = buildModel(rs, modelClass, pageSize);
            if (rs != null)
                rs.close();
            if (pst != null)
                pst.close();
            return new Page(list, pageNumber, pageSize, totalPage, (int) totalRow);
        }
        catch (Exception e) {
            throw new ActiveRecordException(e);
        }
        finally {
            DbKit.close(conn);
        }
    }

    @SuppressWarnings({"rawtypes", "unchecked"})
    public final <T> List<T> buildModel(ResultSet rs,
                                        Class<? extends Model> modelClass,
                                        int pageSize) throws SQLException, InstantiationException,
            IllegalAccessException {
        List<T> result = new ArrayList<T>();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        String[] labelNames = new String[columnCount + 1];
        int[] types = new int[columnCount + 1];
        buildLabelNamesAndTypes(rsmd, labelNames, types);
        for (int k = 0; k < pageSize && rs.next(); k++) {
            Model<?> ar = modelClass.newInstance();
            Map<String, Object> attrs = CPI.getAttrs(ar);
            for (int i = 1; i <= columnCount; i++) {
                Object value;
                if (types[i] < Types.BLOB)
                    value = rs.getObject(i);
                else if (types[i] == Types.CLOB)
                    value = ModelBuilder.handleClob(rs.getClob(i));
                else if (types[i] == Types.NCLOB)
                    value = ModelBuilder.handleClob(rs.getNClob(i));
                else if (types[i] == Types.BLOB)
                    value = ModelBuilder.handleBlob(rs.getBlob(i));
                else
                    value = rs.getObject(i);

                attrs.put(labelNames[i], value);
            }
            result.add((T) ar);
        }
        return result;
    }
}
